/**
 * 
 * @author Karla Barber
 * General Credit Card Class creation
 *
 */
package Classes;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;



public class CreditCard {
	//===============================Properties=======================================
	// start Karla Barber properties
	int ccID, custID;
	String ccNum;
	Date ccDate;
	
	// end Karla Barber properties
	
	//===============================Constructors=======================================
	
	//===============================Getters and Setters================================
	// start Karla Barber getters and setters
	
	public void setCcID(int ccID) {
		this.ccID = ccID;
	}
	public String getCcNum() {
		return ccNum;
	}
	public void setCcNum(String ccNum) {
		this.ccNum = ccNum;
	}
	public int getCustID() {
		return custID;
	}
	public void setCustID(int custID) {
		this.custID = custID;
	}
	public String getCcName() {
		return ccName;
	}
	public void setCcName(String ccName) {
		this.ccName = ccName;
	}
	public Date getCcDate() {
		return ccDate;
	}
	public void setCcDate(Date ccDate) {
		this.ccDate = ccDate;
	}
	public int getCcID() {
		return ccID;
	}
	String ccName;
	
	public CreditCard() {
		super();
	}
	// end Karla Barber getters and setters
	
	//===============================Methods============================================

	//this method retrieves the highest credit card id from the table adds 1 and 
	//returns the new number
	public int nextCCId(){
    	int nextId = 1;
    	try
    	{
    		Class.forName("oracle.jdbc.driver.OracleDriver");
	        Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
			
			if (dbConn != null)
			{
				try {
					//Create a new Statement
					Statement stmnt = dbConn.createStatement();
					ResultSet rs = stmnt.executeQuery("select max(creditcardid) as maxId from ictoosd.creditcards");
					
					
					while (rs.next())
					{
						//grab the last number and add one to it to get next number
						nextId = (rs.getInt("maxId") + 1);
					}
				}
				catch (SQLException e) 
				{
					e.printStackTrace();
				}
				dbConn.close();	
			}
			else
			{
				System.out.println("No SQL connection was made.");
			}
			
			return nextId;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    	}
		return nextId;
		
	}

	//this method receives a customer id, reads the credit card information from the database
	//and returns a credit card object
	public CreditCard retrieveCC(String custId)
	{
		CreditCard cc = new CreditCard();
    	try
    	{
    		Class.forName("oracle.jdbc.driver.OracleDriver");
	        Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
			
			if (dbConn != null)
			{
				try {
					//Create a new Statement
					Statement stmnt = dbConn.createStatement();
					ResultSet rs = stmnt.executeQuery("select creditcardid, ccname, to_char(ccnumber), ccexpiry, customerid from ictoosd.creditcards where customerid = " + Integer.parseInt(custId));
					while (rs.next())
					{
						cc.ccID = rs.getInt(1);
						cc.ccName = rs.getString(2);
						cc.ccNum = rs.getString(3);
						cc.ccDate = rs.getDate(4);
						cc.custID = rs.getInt(5);
					}
				}
				catch (SQLException e) 
				{
					e.printStackTrace();
				}
				dbConn.close();	
			}
			else
			{
				System.out.println("No SQL connection was made.");
			}
			
			return cc;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    	}
		return cc;
	}
	
	//this method saves new credit card information to the database
	public void saveCC()
    {
        try 
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
            String lsql = "insert into ICTOOSD.CREDITCARDS" +
            			  "(CREDITCARDID, CCNAME, CCNUMBER, CCEXPIRY, CUSTOMERID) " +
            			  "Values(" + ccID + ", '" + ccName + "', '" + ccNum + "', to_date('" + ccDate + "','YYYY-MM-DD'), " + custID + ")"; 
                			      
	        Statement addStmnt = dbConn.createStatement();
	        
	        int rowsChanged = addStmnt.executeUpdate(lsql);
	        
	        if (rowsChanged == 0)
	        {
	        	System.out.println("update failed");
	        }
	        else
	        {
	        	System.out.println("inserted: " + rowsChanged + " row(s)");
	        }
	        dbConn.commit();
	        dbConn.close();
        }
        catch (SQLException e) 
        {
                e.printStackTrace();
        } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        }
    }

	//this method updates existing credit card information in the database
	public void updateCC()
    {
        try 
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
            String lsql = "update ICTOOSD.CREDITCARDS " +
            			  "set CCNAME = '" + ccName + "', " + 
            			      "CCNUMBER = '" + ccNum + "', " +
            			      "CCEXPIRY = to_date('" + ccDate + "','YYYY-MM-DD'), " +
            			      "CUSTOMERID = " + custID + 
            			  "where CREDITCARDID = " + ccID;
                			      
            Statement stmt = dbConn.createStatement();
	        int rowsChanged = stmt.executeUpdate(lsql);
	        
	        if (rowsChanged == 0)
	        {
	        	System.out.println("update failed");
	        }
	        else
	        {
	        	System.out.println("updated " + rowsChanged + " row(s)");
	        }
	        dbConn.commit();
	        dbConn.close();
        }
        catch (SQLException e) 
        {
                e.printStackTrace();
        } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        }
    }

	//this method deletes a credit card entry from the database
	public void deleteCC()
    {
        try 
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
            String lsql = "delete ictoosd.creditcards where creditcardid = " + ccID;
                			      
            Statement stmt = dbConn.createStatement();
	        int rowsChanged = stmt.executeUpdate(lsql);
	        
	        if (rowsChanged == 0)
	        {
	        	System.out.println("update failed");
	        }
	        else
	        {
	        	System.out.println("updated " + rowsChanged + " row(s)");
	        }
	        dbConn.commit();
	        dbConn.close();
        }
        catch (SQLException e) 
        {
                e.printStackTrace();
        } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        }
    }
}
